eval 'exec perl -wS $0 ${1+"$@"}'
 if 0;

use strict;
use warnings;
use POSIX qw(strftime);

use DBI();
use File::Basename;
use File::stat;

# Following variable is used as a general file descriptor for all the 
# output of the current tool (the log file). Actually, this file will
# contain the copy of both the stdout and stderr channel.
my $G_LOG;

#my $G_OPL_PATH="/data/samba_share/tmp";
my $G_OPL_PATH="/data/samba_share/tct-huizhou/opl/archives";

################################################################################
#
################################################################################
sub get_time
{
    my $separator=shift @_;
    my @timelist = @_;
    
    my $now_time="";
    
    if (! defined $separator)
    {
	$separator=":";
    }
    
    if (scalar @timelist<1)
    {
	@timelist=localtime();
    }
    
    $now_time = strftime("%H".$separator."%M".$separator."%S",@timelist);
    
    return $now_time;
}

################################################################################
#
################################################################################
sub get_date
{
    my $separator=shift @_;
    my @timelist = @_;
    
    my $now_date="";
    
    if (! defined $separator)
    {
	$separator="/";
    }
    
    if (scalar @timelist<1)
    {
	@timelist=localtime();
    }
    
    $now_date = POSIX::strftime("%Y".$separator."%m".$separator."%d",@timelist);
    
    return $now_date;
}

################################################################################
#
################################################################################
sub mylog
{
    if ($G_LOG)
    {
	print $G_LOG get_date()." ".get_time()." ".join("",@_);
    }
}

################################################################################
#
################################################################################
sub error
{
    print STDERR get_date()." ".get_time()." [ERROR] ",@_;
    mylog "[ERROR] ", @_;
}

################################################################################
#
################################################################################
sub warning
{
    print STDERR get_date()." ".get_time()." [WARNING] ",@_;
    mylog("[WARNING],",@_);
}

################################################################################
#
################################################################################
sub debug
{
    my $h_ref_param=shift @_;
    
    if (exists $h_ref_param->{DEBUG})
    {
	print STDOUT get_date()." ".get_time()." [DEBUG] ",@_;
	mylog("[DEBUG] ",@_);
    }
}

################################################################################
#
################################################################################
sub info
{
    print STDOUT get_date()." ".get_time()." [INFO] ",@_;
    mylog("[INFO] ",@_);
}


################################################################################
#
################################################################################
sub print_h_content
{
    my $h_ref_param=shift @_;
    
    debug $h_ref_param, "Printing Global Hash Content:\n";
    debug $h_ref_param, "######################################################\n";
    foreach my $thekey (keys %$h_ref_param)
    {
	debug $h_ref_param, $thekey." => ".$h_ref_param->{$thekey}."\n";
    }
    debug $h_ref_param, "######################################################\n";
}

################################################################################
#
################################################################################
sub db_connect
{
    my $h_ref_param=shift @_;

    my $dbh = DBI->connect("DBI:mysql:database=".$h_ref_param->{DBNAME}.";host=localhost",
                         $h_ref_param->{DBLOGIN}, $h_ref_param->{DBPASSWD},
                         {'RaiseError' => 0, 'AutoCommit' => 0});

    $h_ref_param->{DB}=$dbh;

    return (defined $dbh);
}

################################################################################
#
################################################################################
sub db_disconnect
{
    my $h_ref_param=shift @_;
    $h_ref_param->{DB}->disconnect();
}

################################################################################
#
################################################################################
sub db_file_already_processed
{
    my $h_ref_param=shift @_;
    my $num_opl = shift @_;
    my $res=0;
    
    my $sth = $h_ref_param->{DB}->prepare("SELECT COUNT(*) FROM opl WHERE num_opl='$num_opl';");
    $sth->execute();
    my $ref = $sth->fetchrow_hashref();

    $sth->finish();

    return $ref->{'COUNT(*)'}!=0;
}

################################################################################
#
################################################################################
sub get_formatted_date
{
    my $h_ref_param=shift @_;
    my $raw_date = shift @_;

    my $formatted_date = "";
    my $year = "";
    my $month = "";
    my $day = "";
    my $hour = "";
    my $minute = "";
    my $second = "";
    
    $year = substr($$raw_date,0,4);
    $month = substr($$raw_date,4,2);
    $day = substr($$raw_date,6,2);
    $hour = substr($$raw_date,8,2);
    $minute = substr($$raw_date,10,2);
    $second = substr($$raw_date,12,2);
    
    $formatted_date = $year. "-" .$month. "-" .$day. " " .$hour. ":" . $minute. ":" .$second;
    return $formatted_date;
}

################################################################################
#
################################################################################
sub db_data_insert
{
    my $h_ref_param=shift @_;
    my $lref_data = shift @_;
    
    my $file_create_date = shift @_;
    my $insert_date = "";
    
    $insert_date=get_date("-")." ".get_time(":");
    my $res=0;
    my $sth;
    my $num_opl = substr(@$lref_data[7],-8,10);
    my $date_pack = get_formatted_date($h_ref_param,\@$lref_data[18]);
    my $date_deliv = get_formatted_date($h_ref_param,\@$lref_data[19]);
    
    # trap errors using eval{}
    eval {
	$sth = $h_ref_param->{DB}->prepare("INSERT INTO opl (insert_date,file_date,ref_com_uc,ref_com_cli,ref_indus_er,num_opl,nb_imei_opl,num_client,num_com_cli,num_com_alc,date_pack,date_deliv,city_deliv,ref_color,fam_prod,code_prod,des_ref_com,ean13) VALUES ( '$insert_date', '$file_create_date','@$lref_data[4]','@$lref_data[5] ','@$lref_data[6] ','$num_opl','@$lref_data[8] ','@$lref_data[9] ','@$lref_data[10] ','@$lref_data[11] ','$date_pack ','$date_deliv ','@$lref_data[20] ','@$lref_data[23] ','@$lref_data[24] ',' @$lref_data[25]',' @$lref_data[26]','@$lref_data[27] ');");
	
	$res=$sth->execute();
	$h_ref_param->{DB}->commit();
    };
    
    if ( $@ )
    {
	error("Transaction aborted: $@\n");
	$h_ref_param->{DB}->rollback();
	return 0;
    }
    
    return $res;
}

################################################################################
#
################################################################################
sub db_get_opl_id
{
    my $h_ref_param=shift @_;
    my $num_opl = shift @_;
    my $res=0;
    my $sth;
    
    $sth = $h_ref_param->{DB}->prepare("SELECT id FROM opl WHERE num_opl='$num_opl';");
    $sth->execute();
    my $ref = $sth->fetchrow_hashref();
    
    $sth->finish();
    
    return $ref->{'id'};
}

################################################################################
#
################################################################################
sub db_imei_insert
{
    my $h_ref_param=shift @_;
    my $lref_imei = shift @_;
    my $opl_id = shift @_;
    my $res=0;
    my $sth;
    
    # trap errors using eval{}
    eval {
	foreach my $myline (@$lref_imei)
	{
	    $sth = $h_ref_param->{DB}->prepare("INSERT INTO imei (imei,opl_id) VALUES ( '$myline ', '$opl_id' );");
	    
	    $res=$sth->execute();
	}
    };
    
    if ( $@ )
    {
	error("Transaction aborted: $@\n");
	$h_ref_param->{DB}->rollback();
	return 0;
    }

    return $res;
    #return 1;
}

################################################################################
#
################################################################################
sub file_move
{
    #my $h_ref_param=shift @_;
    
    #return 1;
    
    
    my $h_ref_param=shift @_;
    my $source_file=shift @_;
    my $dest_file=shift @_;
    my $file_type=shift @_;
    
    my $cmd="";
    my @output=();
    
    $cmd="mv -f \"$source_file\" \"$dest_file\"";
    #$cmd="cp -f \"$source_file\" \"$dest_file\"";
    
    debug $h_ref_param, "Executing in 'file_move' function:>$cmd<\n";
    @output=`$cmd`;
    
    if ($? != 0)
    {
	error("Failed to move $file_type to its final location. Produced operation output is:\n");
	error(@output);
	return 0;
    }
    
    return 1;
}

################################################################################
#
################################################################################
sub get_file_list
{
    my $h_ref_param=shift @_;
    my $local_dir=shift @_;

    my @output=();

    my $cmd="";

    #$cmd="find \"/data/samba_share/tct-huizhou/opl/imported\" -name \"*.000\" -print";
    $cmd="find \"/data/samba_share/tct-huizhou/opl/archives/imported\" -name \"*.000\" -print";
    
    debug $h_ref_param, "Executing in 'get_file_list' function:>$cmd<\n";
    @output=`$cmd`;

    if ( ($? != 0) )
    {
	error("Failed to execute >$cmd<. Produced output is:\n");
	error(@output);
	error("End of output. Skipping current file processing ...\n");
	return 0;
    }
    
    # Success !
    return @output;
}

################################################################################
#
################################################################################
sub get_content
{
    my $h_ref_param=shift @_;
    my $file = shift @_;
    my $line = "";
    my $cmd = "";
    my $length = "";
    my $lref_content = shift @_;
    my $ref_data = shift @_;
    my $opl_file = shift @_;
    my $field = "";
    my @fields = ();
    my $block1 = "";
    my $block2 = "";
    my $block3 = "";
    my $block4 = "";
    my $block5 = "";
    my $block6 = "";
    my $block7 = "";
    
    @$lref_content = ();
    $$ref_data = ();
    @$opl_file = ();
    
    open(FILE, "<$file");
    @$lref_content = <FILE>;

    foreach $line (@$lref_content)
    {
	$line=~s/\r\n$//;
	$line=~s/\n\r$//;
	$line=~s/\n$//;
	$line=~s/\r$//;
	
	chomp($line);
	
	$length = length($line);
	
	if (($length != 495) && ($length != 511) && ($length != 527))
	{
	    debug $h_ref_param, "File " .$file. " length = " .$length. "\n";
	    return 0;
	} 
	
	# check if field opl_number is numeric
	#@fields = split (';',$line);
	
	#$line =~/^(.{15};.{3};.{3};.{20};.{18};.{20};.{18};)(.{10})(;.+)/;
	$line =~/^(.{15};.{3};.{3};.{20};.{18};.{20};.{18};)(.{10})(;.{6};.{13};.{10};.{10};)(.{15})(;.{15};.{6};)(.{15})(;.+)/;
	$block1 = $1;
	$block2 = $2;
	$block3 = $3;
	$block4 = $4;
	$block5 = $5;
	$block6 = $6;
	$block7 = $7;

	$block2 =~s/[a-zA-Z]/0/g;
	$block4 =~s/[a-zA-Z]/0/g;
	$block6 =~s/[a-zA-Z]/0/g;

	#print $header.$block.$end. "\n";
	push (@$opl_file,$block1.$block2.$block3.$block4.$block5.$block6.$block7);
	#print $line. "\n";
    }
    debug $h_ref_param, "File " .$file. " length = " .$length. "\n";	
    #@$lref_content= $line;
    $$ref_data = @$lref_content[0];


    return 1;
}

################################################################################
#
################################################################################
sub file_remove
{
    my $h_ref_param=shift @_;
    my $source_file=shift @_;

    my $cmd="";
    my @output=();

    $cmd="rm -f \"$source_file\"";
    debug $h_ref_param, "Executing in 'file_remove' command:>$cmd<\n";
    @output=`$cmd`;

    if ($? != 0)
    {
	error("Failed to remove $source_file.\n");
	error(@output);
	return 0;
    }

    return 1;
}
################################################################################
#
################################################################################
sub process
{
    my $h_ref_param=shift @_;
    my $err=0;
    my $total_processed_file_counter=0;
    my $skipped_processed_file_counter=0;
    my $error_processed_file_counter=0;
    my $new_processed_file_counter=0;

    my $imei="";
    my @loplcontent = ();
    my $opldata = "";
    my @lopldata = ();
    my @loplimei = ();
    my $opl_id = "";
    my @opl_file= ();
    my $new_opl_file = "";
    
    print_h_content($h_ref_param);
    
    if (! db_connect($h_ref_param))
    {
	error("Failed to connect to database.\n");
	return 1;
    }
    
    # Checking lock file existence first in order to avoid to run two same
    # instance of the tool in parallel.
    info "Checking lock file ... \n";
    if ( -f $h_ref_param->{LOCK_FILE})
    {
	info "Checking lock exits! Aborting ... \n";
	return 1;
    }
    
    info "Creating lock file ... \n";
    `touch $h_ref_param->{LOCK_FILE}`;
    if ($? != 0)
    {
	error("Failed to create lock file >$h_ref_param->{LOCK_FILE}< \n");
	return 1;
    }
    
    my @filelist=();
    
    info "Listing OPL files in $G_OPL_PATH/imported ...\n";
    
    @filelist = get_file_list($h_ref_param,"$G_OPL_PATH");
    
    if (scalar @filelist == 0)
    {
	info "No OPL File Found.  \n";
	info "End of Process. \n";
    } 
    else 
    {
	
	info "Processing files ... \n";
	
	foreach my $file (@filelist)
	{
	    $total_processed_file_counter++;
	    chomp($file);
	    @loplimei = ();
	    @lopldata = ();
	    $opl_id = 0;

	    my ($name,$path,$suffix) = fileparse("$file",());
	    my $file_create_date="";
	    my $mystat = stat($file);

	    info "OPL file found: $name \n";

	    $file_create_date = get_date("-",localtime($mystat->mtime))." ".get_time(":",localtime($mystat->mtime));
	    debug $h_ref_param, "File created date: " .$file_create_date. "\n";

	    info "Extracting content ... \n";

	    if (! get_content($h_ref_param,$file,\@loplcontent,\$opldata,\@opl_file))
	    {
		$error_processed_file_counter++;
		error("File $name may be wrong. Will be moved to $G_OPL_PATH/error \n");
		#file_move($h_ref_param);
		file_move($h_ref_param, "$file", "$G_OPL_PATH/error","OPL file '$file'");
		next;
	    }

	    #info "Extract terminated. \n";

	    @lopldata = split(/;/, $opldata);
	    
	    foreach my $myline (@loplcontent)
	    {
		my @myline = split (/;/, $myline);
		push (@loplimei,$myline[0]);
	    }

	    if (db_file_already_processed($h_ref_param,substr($lopldata[7],-8,10)))
	    {
		#debug $h_ref_param, "File $name already uploaded. Skipping ... \n";
		warning "File $name already uploaded. Skipping ... \n";
		$skipped_processed_file_counter++;
		#file_move($h_ref_param);
		file_move($h_ref_param, "$file", "$G_OPL_PATH/skipped","OPL file '$file'");
		next;
	    } 
	   
	    info "Inserting data into DB ... \n";

	    if (! db_data_insert($h_ref_param,\@lopldata,$file_create_date))
	    {
		error("Couldn't insert data from file $name to DB. \n");
		$error_processed_file_counter++;
		file_move($h_ref_param, "$file", "$G_OPL_PATH/error","OPL file '$file'");
		next;
	    }

	    #info "Data inserted. \n";
	    info "Getting OPL_ID ... \n";

	    $opl_id = db_get_opl_id($h_ref_param,substr($lopldata[7],-8,10));
	    if (($opl_id == 0) || ($opl_id eq ""))
	    {
	    error("OPL_ID equal 0 Couldn't insert imei from file $name to DB. \n");
	    $error_processed_file_counter++;
	    next;
	    }

	    info "Inserting IMEI List into DB ... \n";
	    
	    if (! db_imei_insert($h_ref_param,\@loplimei,$opl_id))
	    {
		error("Couldn't insert imei from file $name to DB. \n");
		$error_processed_file_counter++;
		file_move($h_ref_param, "$file", "$G_OPL_PATH/error","OPL file '$file'");
		next;
	    }
	    
	    #info "List inserted. \n";

	    #debug $h_ref_param, "ldata: ".\@ldata." -> ".join(",",@ldata)."\n";
	    
	    $new_processed_file_counter++; 
	    
	    #debug $h_ref_param, "Moving file $name ... \n";
	    
	    $new_opl_file = $name. ".tmp";
	    info "Creating new OPL file $new_opl_file in $G_OPL_PATH/inserted  ... \n";
	    
	    open (FILE,">$G_OPL_PATH/inserted/$new_opl_file");
	    
	    foreach my $line (@opl_file)
	    {
		print FILE $line. "\n";
		
	    }
	    
	    close (FILE);

	    #info "File created. \n";

	    info "Renaming new file $new_opl_file to $name ... \n";
	    if (! file_move($h_ref_param, "$G_OPL_PATH/inserted/$new_opl_file", "$G_OPL_PATH/inserted/$name","OPL file '$file'"))
	    {
		error("File $new_opl_file couldn't be renamed\n");
	    }
	    #info "File renamed. \n";

	    info "Removing old file $name ... \n";
	    #file_move($h_ref_param, "$G_OPL_PATH/in/$name", "$G_OPL_PATH/in/$new_opl_file","OPL file '$file'");
	    
	    if (! file_remove($h_ref_param, "$G_OPL_PATH/imported/$name"))
	    #if (! file_remove($h_ref_param, "$file"))
	    {
		error("File $name couldn't be removed\n");
	    }
	    
	    #info "File removed. \n";

	    #sleep(3);

	} # foreach my $file (@filelist)
	
	info "End of Process. \n";

    } # if (scalar @filelist == 0)
    
    info "Delete lock file ... \n";
    `rm -f $h_ref_param->{LOCK_FILE}`;
    
    info("### Summary: #################################################\n");
    info("Total OPL file Processed: $total_processed_file_counter\n");
    info("        Included skipped: $skipped_processed_file_counter\n");
    info("          Included error: $error_processed_file_counter\n");
    info("            Included new: $new_processed_file_counter\n");
    info("##################################\n");
    
    if (! db_disconnect($h_ref_param))
    {
	error("Failed to disconnect to database.\n");
	return 1;
    }
    
    return $err;
}

################################################################################
#
################################################################################
sub print_version
{
  my $h_ref_param=shift @_;
  print $h_ref_param->{EXECUTABLE_NAME}." ".$h_ref_param->{VERSION}." ".$h_ref_param->{RELEASE_DATE}."\n";
}

################################################################################
#
################################################################################
sub parse_args
{
    my $h_ref_param=shift @_;
    
    my $arg="";
    my $err=0;
    
    while ($arg=shift @ARGV)
    {
	chomp($arg);
	if ( $arg =~ /^-/ )
	{
	    if ( ($arg =~ /^-h/) || ($arg =~ /^--help/))
	    {
		print_usage($h_ref_param);
		exit(0);
	    }
	    elsif ( ($arg =~ /^-d/) || ($arg =~ /^--debug/))
	    {
		$h_ref_param->{DEBUG}=1;
	    }
	    elsif ( ($arg =~ /^-v/) || ($arg =~ /^--version/))
	    {
		print_version();
		exit(0);
	    }
	    
	    elsif ( ($arg =~ /^-l/) || ($arg =~ /^--log/))
	    {
		$arg=shift @ARGV;
		$h_ref_param->{LOG}=$arg;
	    }

	    else
	    {
		error "Unexpected option >$arg<.\n";
		$err++;
		next;
	    } 
	}
	else
	{
	    #<dbname>: Mandatory. Local database name to use <dbname> database.\n";
	    if (! exists $h_ref_param->{DBNAME})
	    {
		$h_ref_param->{DBNAME}=$arg;
	    }
	    #<dblogin>: Mandatory. Local database login to use <dbname> database.\n";
	    elsif (! exists $h_ref_param->{DBLOGIN})
	    {
		$h_ref_param->{DBLOGIN}=$arg;
	    }
	    #<dbpasswd>: Mandatory. Local database password to use <dbname> database.\n";
	    elsif (! exists $h_ref_param->{DBPASSWD})
	    {
		$h_ref_param->{DBPASSWD}=$arg;
	    }
	}
    }
    
    if (! exists $h_ref_param->{DBPASSWD})
    {
	error("Missing arguments.");
	$err++;
    }
    
    if ($err>0)
    {
	error "Aborting due to previous error(s). Use -h or --help for usage.\n";
	return 1;
    }
    
    return 0;
}
    
################################################################################
#
################################################################################
sub print_usage
  {
    my $h_ref_param=shift @_;

    print "USAGE: ".$h_ref_param->{EXECUTABLE_NAME}." [-h|--help] [-d|--debug] [-v|--version] [-l|--log <log file>] ...] <dbname> <dblogin> <dbpasswd>\n";
    print "\n";
    print "PURPOSE:\n";
    print "   Insert OPL files data into the database \n";
    print "\n";
    print "PARAMETERS DESCRIPTION:\n";
    print "\n";
    print "   <dbname>: Mandatory. Local database name to use <dbname> database.\n";
    print "\n";
    print "   <dblogin>: Mandatory. Local database login to use <dbname> database.\n";
    print "\n";
    print "   <dbpasswd>: Mandatory. Local database password to use <dbname> database.\n";
    print "\n";
    print "OPTIONS DESCRIPTION:\n";
    print "   [-h|--help]:\n";
    print "      Print this usage message and exit.\n";
    print "\n";
    print "   [-v|--version]:\n";
    print "      Print the version number and exit.\n";
    print "\n";
    print "   [-d|--debug]:\n";
    print "      Print excessive output to understand the processing.\n";
    print "\n";
    print "\n";
  }

################################################################################
#
################################################################################
sub open_log
{
    my $h_ref_param=shift @_;

    if (! open($G_LOG,">>".$h_ref_param->{LOG}))
    {
	warning "Cannot create log file ".$h_ref_param->{LOG}." in current directory.\n";

	$h_ref_param->{LOG}="/tmp/".$h_ref_param->{EXECUTABLE_NAME}.".log";

	if (! open($G_LOG,">".$h_ref_param->{LOG}))
	{
	    warning "Still cannot create alternative log file ".$h_ref_param->{LOG}.". No log will be performed.\n";
	    $G_LOG=0;
	}
    }
}


################################################################################
#
################################################################################
sub intro
  {
    my $h_ref_param=shift @_;
    my $err=0;
    my $date_for_file="";

    # Updating some global variables.

    $h_ref_param->{START_TIME}=get_date("-")." ".get_time(":");

    $date_for_file=get_date("-")."_".get_time("-");
    
    $h_ref_param->{VERSION}="0.1.0";
    $h_ref_param->{EXECUTABLE_NAME}="inter_local_opl_v0.1.0.pl";

    $h_ref_param->{LOG}=$h_ref_param->{EXECUTABLE_NAME}.".$date_for_file.log";

    $h_ref_param->{LOCK_FILE}="/tmp/".$h_ref_param->{EXECUTABLE_NAME}.".lock";
    
    $h_ref_param->{RELEASE_DATE}="2007/05/21";

    # Following instruction forces flushing for each output to STDOUT or STDERR.
    $|=1;

    return 0;
  }

################################################################################
#
################################################################################
sub outtro
{
    my $h_ref_param=shift @_;
    if ($G_LOG)
    {
	close($G_LOG);
    }

    return 0;
}

################################################################################
#
################################################################################
sub main
{
  # This hash list will be used to store input parameters and some other
  # usefull information.
  # The actual valable keys are:
  #   VERSION:           string: current version of the tool.
  #   START_TIME:        string: start time of the script in the form YY_MM_DD.HH.MM.SS
  #   EXECUTABLE_NAME:   string: name of the current script.
  #   LOG:               string: name of the log file.
  #   RELEASE_DATE:      string: date of current tool release in form: YYYY/MM/DD
  #   DEBUG:             boolean: if this key exists, then the current script is run in
  #                               debug mode.
  #
  my %params=();
  
  if (intro(\%params)!=0)
  {
      error "Error while executing intro ... Aborting.\n";
      exit(1);
  }

  if (parse_args(\%params)!=0)
  {
      error "Error while parsing arguments ... Aborting.\n";
      exit(1);
  }
  
  open_log(\%params);

  if (process(\%params)!=0)
  {
      error "Error while processing ... Aborting.\n";
      exit(1);
  }

  if (outtro(\%params)!=0)
  {
      error "Error while executing outtro ... Aborting.\n";
      exit(1);
  }


}


################################################################################
#
################################################################################

main();
exit(0);

